﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
///Class1 的摘要说明
/// </summary>
public class Class1
{
	public Class1()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}
    public SqlConnection getcon()
    {
        string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
        ///创建连接
        SqlConnection mycon = new SqlConnection(connectionString);
        return mycon;
    }
    public DataSet GetMessages()
        {	///获取连接字符串
            string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
            ///创建连接
            SqlConnection con = new SqlConnection(connectionString);
            ///创建SQL语句
            string cmdText = "SELECT * FROM Message Order by CreateDate DESC";
            ///创建SqlDataAdapter
            SqlDataAdapter da = new SqlDataAdapter(cmdText, con);
            ///定义DataSet
            DataSet ds = new DataSet();
            try
            {   ///打开连接
                con.Open();
                ///填充数据
                da.Fill(ds, "DataTable");
            }
            catch (Exception ex)
            {   ///抛出异常
                throw new Exception(ex.Message, ex);
            }
            finally
            {   ///关闭连接
                con.Close();
            }

            return ds;
        }
    public int ExecSql(string sqlStr)//ExecSql方法是使用sqlconnand对象数据库执行update，insert，delete等
    {
        SqlConnection mycon = getcon();
        mycon.Open();
        SqlCommand mycmd = new SqlCommand(sqlStr, mycon);
        try
        {
            mycmd.ExecuteNonQuery();//执行sql语句并返回受影响的行数
            mycon.Close();
        }
        catch
        {
            mycon.Close();
            return 0;
        }
        return 1;
    }
        public int AddMessage(string title, string message, string ip, string email)
        {	///获取连接字符串
            string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
            ///创建连接
            SqlConnection con = new SqlConnection(connectionString);
            ///创建SQL语句
            string cmdText = "INSERT INTO Message(Title,Message,IP,Email,CreateDate,Status)VALUES(@Title,@Message,@IP,@Email,GETDATE(),0)";
            ///创建SqlCommand
            SqlCommand cmd = new SqlCommand(cmdText, con);
            ///创建参数并赋值
            cmd.Parameters.Add("@Title", SqlDbType.VarChar, 200);
            cmd.Parameters.Add("@Message", SqlDbType.Text);
            cmd.Parameters.Add("@Ip", SqlDbType.VarChar, 20);
            cmd.Parameters.Add("@Email", SqlDbType.VarChar, 255);
            cmd.Parameters[0].Value = title;
            cmd.Parameters[1].Value = message;
            cmd.Parameters[2].Value = ip;
            cmd.Parameters[3].Value = email;

            int result = -1;
            try
            {   ///打开连接
                con.Open();
                ///操作数据
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {   ///抛出异常
                throw new Exception(ex.Message, ex);
            }
            finally
            {   ///关闭连接
                con.Close();
            }

            return result;
        }

        public int DeleteMessage(int messageID)
        {	///获取连接字符串
            string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
            ///创建连接
            SqlConnection con = new SqlConnection(connectionString);
            ///创建SQL语句
            string cmdText = "DELETE Message WHERE ID = @ID";
            ///创建SqlCommand
            SqlCommand cmd = new SqlCommand(cmdText, con);
            ///创建参数并赋值
            cmd.Parameters.Add("@ID", SqlDbType.Int, 4);
            cmd.Parameters[0].Value = messageID;

            int result = -1;
            try
            {   ///打开连接
                con.Open();
                ///操作数据
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {   ///抛出异常
                throw new Exception(ex.Message, ex);
            }
            finally
            {   ///关闭连接
                con.Close();
            }

            return result;
        }

        public DataSet GetReplyByMessage(int messageID)
        {	///获取连接字符串
            string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
            ///创建连接
            SqlConnection con = new SqlConnection(connectionString);
            ///创建SQL语句
            string cmdText = "SELECT * FROM Reply,Message WHERE MessageID = Message.ID and MessageID=" + messageID;
            ///创建SqlDataAdapter
            SqlDataAdapter da = new SqlDataAdapter(cmdText, con);
            ///创建参数并赋值
            da.SelectCommand.Parameters.Add("@MessageID", SqlDbType.Int, 4);
            da.SelectCommand.Parameters[0].Value = messageID;
            ///定义DataSet
            DataSet ds = new DataSet();
            try
            {   ///打开连接
                con.Open();
                ///填充数据
                da.Fill(ds, "DataTable");
            }
            catch (Exception ex)
            {   ///抛出异常
                throw new Exception(ex.Message, ex);
            }
            finally
            {   ///关闭连接
                con.Close();
            }

            return ds;
        }

        public int AddReply(string message, string ip, int messageID)
        {	///获取连接字符串
            string connectionString = ConfigurationManager.ConnectionStrings["CSConnectionString"].ConnectionString;
            ///创建连接
            SqlConnection con = new SqlConnection(connectionString);
            ///创建SQL语句
            string cmdText = "INSERT INTO Reply(Reply,IP,CreateDate,MessageID)VALUES(@Reply,@IP,GETDATE(),@MessageID)";
            ///创建SqlCommand
            SqlCommand cmd = new SqlCommand(cmdText, con);
            ///创建参数并赋值
            cmd.Parameters.Add("@Reply", SqlDbType.VarChar, 1000);
            cmd.Parameters.Add("@Ip", SqlDbType.VarChar, 20);
            cmd.Parameters.Add("@MessageID", SqlDbType.Int, 4);
            cmd.Parameters[0].Value = message;
            cmd.Parameters[1].Value = ip;
            cmd.Parameters[2].Value = messageID;

            int result = -1;
            try
            {   ///打开连接
                con.Open();
                ///操作数据
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {   ///抛出异常
                throw new Exception(ex.Message, ex);
            }
            finally
            {   ///关闭连接
                con.Close();
            }

            return result;
        }
    }


